#!/usr/bin/python3

import pymysql
import sys
import xlwt

host = input('请输入数据库地址:')
database = input('请输入数据库名字:')
username = input('请输入数据库账号:')
password = input('请输入数据库密码:')

# 打开数据库连接
db = pymysql.connect(host=host, user=username, password=password)
# 创建一个workbook 设置编码
workbook = xlwt.Workbook(encoding='utf-8')
style = xlwt.XFStyle()  # 正文样式
style_title1 = xlwt.XFStyle()  # 一级标题样式
style_title2 = xlwt.XFStyle()  # 二级标题样式

al = xlwt.Alignment()
al.horz = 0x02  # 设置水平居中
al.vert = 0x01  # 设置垂直居中
style.alignment = al
style_title1.alignment = al
style_title2.alignment = al
# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()

# 查询数据表
cursor.execute(
    "SELECT  TABLE_NAME,TABLE_COMMENT FROM INFORMATION_SCHEMA.TABLES  WHERE TABLE_SCHEMA = '%s' group by TABLE_NAME,TABLE_COMMENT" % (
        database))
for table in cursor.fetchall():
    if len(table[1]) == 0:
        table_name = '未定义_%s' % (table[0])
    else:
        table_name = table[1][:10]
    worksheet = workbook.add_sheet('%s' % (table_name))
    worksheet.write_merge(0, 0, 0, 6, '%s(%s)' % (table_name, table[0]), style)  # Merges row 0's columns 0 through 3.
    worksheet.write(1, 0, '序号', style)
    worksheet.write(1, 1, '字段名', style)
    worksheet.write(1, 2, '注释', style)
    worksheet.write(1, 3, '数据类型', style)
    worksheet.write(1, 4, '字段长度', style)
    worksheet.write(1, 5, '是否可为空', style)
    # 使用 execute()  方法执行 SQL 查询
    cursor.execute("SELECT COLUMN_NAME, column_comment, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,IS_NULLABLE, COLUMN_DEFAULT \
      FROM INFORMATION_SCHEMA.COLUMNS  WHERE TABLE_NAME='%s' and TABLE_SCHEMA = '%s'" % (table[0], database))

    rowindex = 2
    # 使用 fetchone() 方法获取单条数据.
    for data in cursor.fetchall():
        worksheet.write(rowindex, 0, rowindex - 1, style)
        worksheet.write(rowindex, 1, data[0], style)
        worksheet.write(rowindex, 2, data[1], style)
        worksheet.write(rowindex, 3, data[2], style)
        worksheet.write(rowindex, 4, data[3], style)
        worksheet.write(rowindex, 5, data[4], style)
        rowindex += 1

# 关闭数据库连接
db.close()
workbook.save('%s.xls'%(database))
print('数据库字典生成成功，文件名%s.xls'%(database))
